﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;
using System.Configuration;


namespace PMS.DAL
{
    public class ScheduleStatementDAL
    {
        #region Common
        private static ScheduleStatementENT CreateScheduleStatementFromReader(IDataReader reader)
        {
            ScheduleStatementENT item = new ScheduleStatementENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.ScheduleStatementID.ToString()))) item.ScheduleStatementID = Utility.IntParse(reader[ScheduleStatementColumns.ScheduleStatementID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.ScheduleItemID.ToString()))) item.ScheduleItemID = Utility.IntParse(reader[ScheduleStatementColumns.ScheduleItemID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.GroupID.ToString()))) item.GroupID = Utility.IntParse(reader[ScheduleStatementColumns.GroupID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.Mark.ToString()))) item.Mark = Utility.IntParse(reader[ScheduleStatementColumns.Mark.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[ScheduleStatementColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[ScheduleStatementColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[ScheduleStatementColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[ScheduleStatementColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ScheduleStatementColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[ScheduleStatementColumns.Isdeleted.ToString()]);


            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static ScheduleStatementENT GetScheduleStatementByID(int scheduleStatementID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementGetByID.ToString());
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.Int32, scheduleStatementID);

                ScheduleStatementENT scheduleStatement = new ScheduleStatementENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        scheduleStatement = CreateScheduleStatementFromReader(reader);
                    }
                    reader.Close();
                }

                return scheduleStatement;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleStatementByID");
            }
        }

        public static ScheduleStatementENT GetScheduleStatementByGroupAndScheduleItem(int groupID, int scheduleItemID, out int scheduleID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementGetByGroupAndScheduleItem.ToString());
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.Int32, scheduleItemID);
                database.AddOutParameter(dbCommand, "@ScheduleID", DbType.Int32, 0);
                ScheduleStatementENT scheduleStatement = new ScheduleStatementENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        scheduleStatement = CreateScheduleStatementFromReader(reader);
                    }
                    reader.Close();
                }
                scheduleID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleID"));
                return scheduleStatement;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleStatementByID");
            }
        }

        public static ScheduleStatementCollection GetScheduleStatementList(string scheduleStatementName, int projectID, int scheduleID, int userID, string status, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementGetList.ToString());
                ScheduleStatementCollection listScheduleStatement = new ScheduleStatementCollection();
                database.AddInParameter(dbCommand, "@ScheduleStatementName", DbType.String, scheduleStatementName);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleStatement.Add(CreateScheduleStatementFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listScheduleStatement;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleStatementList");
            }
        }

        public static ScheduleStatementCollection GetScheduleStatementListForCMB(int userID, int projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);

                ScheduleStatementCollection listScheduleStatement = new ScheduleStatementCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleStatement.Add(CreateScheduleStatementFromReader(reader));
                    }
                    reader.Close();
                }

                return listScheduleStatement;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleStatementListForCMB");
            }
        }

        public static ScheduleStatementCollection GetScheduleStatementListByScheduleIDForCMB(int scheduleID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementGetListByScheduleIDForCMB.ToString());
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);

                ScheduleStatementCollection listScheduleStatement = new ScheduleStatementCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleStatement.Add(CreateScheduleStatementFromReader(reader));
                    }
                    reader.Close();
                }

                return listScheduleStatement;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleStatementListByScheduleIDForCMB");
            }
        }

        public static ScheduleStatementCollection GetScheduleStatementListByStatus(string scheduleStatementID, string status)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementGetListByStatus.ToString());
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.String, scheduleStatementID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);

                ScheduleStatementCollection listScheduleStatement = new ScheduleStatementCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleStatement.Add(CreateScheduleStatementFromReader(reader));
                    }
                    reader.Close();
                }

                return listScheduleStatement;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleStatementListForCMB");
            }
        }


        public static ScheduleStatementCollection GetScheduleStatementListByOwnerAndStatus(int userID, string status, string scheduleStatementID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementGetListByOwnerAndStatus.ToString());

                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.String, scheduleStatementID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                ScheduleStatementCollection listScheduleStatement = new ScheduleStatementCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listScheduleStatement.Add(CreateScheduleStatementFromReader(reader));
                    }
                    reader.Close();
                }

                return listScheduleStatement;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetScheduleStatementListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertScheduleStatement(ScheduleStatementENT scheduleStatement)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementInsert.ToString());
                int scheduleStatementID = 0;
                database.AddOutParameter(dbCommand, "@ScheduleStatementID", DbType.Int32, scheduleStatement.ScheduleStatementID);
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.Int32, scheduleStatement.ScheduleItemID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, scheduleStatement.GroupID);
                database.AddInParameter(dbCommand, "@Mark", DbType.Decimal, scheduleStatement.Mark);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, scheduleStatement.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, scheduleStatement.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, scheduleStatement.Isdeleted);

                database.ExecuteNonQuery(dbCommand);
                scheduleStatementID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ScheduleStatementID"));
                return scheduleStatementID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertScheduleStatement");
            }
        }


        public static int UpdateScheduleStatement(ScheduleStatementENT scheduleStatement)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementUpdate.ToString());

                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.Int32, scheduleStatement.ScheduleStatementID);
                database.AddInParameter(dbCommand, "@ScheduleItemID", DbType.Int32, scheduleStatement.ScheduleItemID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, scheduleStatement.GroupID);
                database.AddInParameter(dbCommand, "@Mark", DbType.Decimal, scheduleStatement.Mark);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, scheduleStatement.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, scheduleStatement.Isdeleted);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return scheduleStatement.ScheduleStatementID;
                }
                return 0;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertScheduleStatement");
            }
        }


        public static int DeleteScheduleStatement(string scheduleStatementID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementDelete.ToString());
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.String, scheduleStatementID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertScheduleStatement");
            }
        }


        public static int ConfirmScheduleStatement(string scheduleStatementID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procScheduleStatementConfirm.ToString());
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.String, scheduleStatementID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_ConfirmScheduleStatement");
            }
        }
        #endregion


    }
}
